import mysql.connector
from mysql.connector import Error
import json
from typing import Optional, Dict, Any
import decimal


class DatabaseManager:
    _instance = None
    _connection = None
    _cursor = None

    def __new__(cls):
        if cls._instance is None:
            cls._instance = super(DatabaseManager, cls).__new__(cls)
        return cls._instance

    def __init__(self):
        self.db_config = {
            'host': 'localhost',
            'user': 'root',
            'password': '20539',
            'database': 'smart_supermarket'
        }

    def connect(self):
        """建立数据库连接"""
        try:
            if not self._connection or not self._connection.is_connected():
                self._connection = mysql.connector.connect(**self.db_config)
                self._cursor = self._connection.cursor(dictionary=True)
        except Error as e:
            print(f"数据库连接错误: {e}")
            raise

    def get_connection(self):
        """获取数据库连接"""
        self.connect()
        return self._connection

    def get_cursor(self):
        """获取数据库游标"""
        self.connect()
        return self._cursor

    def execute_query(self, query: str, params: tuple = None) -> Optional[list]:
        """执行查询并返回结果"""
        try:
            self.connect()
            self._cursor.execute(query, params)
            results = self._cursor.fetchall()
            return self._process_results(results)
        except Error as e:
            print(f"查询执行错误: {e}")
            raise

    def execute_update(self, query: str, params: tuple = None) -> bool:
        """执行更新操作"""
        try:
            self.connect()
            self._cursor.execute(query, params)
            self._connection.commit()
            return True
        except Error as e:
            print(f"更新执行错误: {e}")
            self._connection.rollback()
            raise

    def _process_results(self, results: list) -> list:
        """处理查询结果，转换特殊类型"""
        processed_results = []
        for row in results:
            processed_row = {}
            for key, value in row.items():
                if isinstance(value, decimal.Decimal):
                    processed_row[key] = float(value)
                else:
                    processed_row[key] = value
            processed_results.append(processed_row)
        return processed_results

    def close(self):
        """关闭数据库连接"""
        try:
            if self._cursor:
                self._cursor.close()
            if self._connection and self._connection.is_connected():
                self._connection.close()
        except Error as e:
            print(f"关闭连接错误: {e}")

    def __del__(self):
        """析构函数，确保连接被正确关闭"""
        self.close()
